package dao.impl;

import core.BaseDao;
import dao.OrderFoodBkDao;
import org.apache.commons.dbutils.handlers.*;
import pojo.Food;
import pojo.OrderFood;
import pojo.OrderFoodBk;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OrderFoodBkDaoImpl extends BaseDao implements OrderFoodBkDao {
    //static StringBuffer sb;
    static String NOSORTFOODIDS;
    static {
        FoodDaoImpl foodDao = new FoodDaoImpl();
        StringBuffer sb = new StringBuffer("(");
        List<Object> noSortFoodIds = foodDao.findNoSortFoodId();
        for (Object id : noSortFoodIds) {
            sb.append(id+",");
        }
        sb.replace(sb.length()-1,sb.length(),")");
        NOSORTFOODIDS=sb.toString();
    }
    /**
     * 判断已经存在备份
     * @param orderId
     * @param foodId
     * @return
     */
    @Override
    public Integer isBkExist(String orderId, String foodId) {
        try {
            sql = "SELECT * FROM order_food_bk WHERE order_id=? AND food_id=?";
            OrderFoodBk query = queryRunner.query(sql, new BeanHandler<>(OrderFoodBk.class, rowProcessor), orderId, foodId);
            return query==null ? 0 : 1;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    /**
     * 修改备份信息
     * @param orderId
     * @param foodId
     * @param foodCount
     * @return
     */
    @Override
    public Integer updateBk(String orderId, String foodId, String foodCount) {
        try {
            sql = "UPDATE order_food_bk SET food_count_bk=? WHERE order_id=? AND food_id=?";
            return queryRunner.update(sql, foodCount, orderId, foodId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /** 添加备份信息
     * @param orderId
     * @param foodId
     * @param foodCount
     * @return
     */
    @Override
    public Integer addBk(String orderId, String foodId, String foodCount) {
        try {
            sql = "INSERT INTO order_food_bk(order_id, food_id, food_count_bk) VALUES(?,?,?)";
            return queryRunner.update(sql, orderId, foodId, foodCount);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }


    /**
     * 获取食品的销量
     * @return
     */
    @Override
    public List<String> findSumCount() {
        try {
            sql = "SELECT SUM(order_food_bk.food_count_bk) as food_page FROM order_food_bk GROUP BY order_food_bk.food_id HAVING order_food_bk.food_id NOT IN "+NOSORTFOODIDS+ "ORDER BY SUM(order_food_bk.food_count_bk) DESC";
            List<Object[]> query = queryRunner.query(sql, new ArrayListHandler());
            List<String> countList = new ArrayList<>();
            for (Object[] o : query) {
                countList.add(o[0].toString());
            }
            return countList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }

    /**
     * 按销量排序获取所有食品
     * @return
     */
    @Override
    public List<Food> findSumCountFoodId() {
        try {
            sql = "SELECT * FROM order_food_bk GROUP BY order_food_bk.food_id HAVING order_food_bk.food_id NOT IN"+NOSORTFOODIDS+ "ORDER BY SUM(order_food_bk.food_count_bk) DESC";
            return queryRunner.query(sql, new BeanListHandler<>(Food.class, rowProcessor));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }

    /**
     * 根据食品id查找销量
     * @param minId
     * @param maxId
     * @param food_id
     * @return
     */
    @Override
    public Integer findSumCountById(String minId, String maxId, String food_id) {
        try {
            sql = "SELECT SUM(food_count_bk) FROM order_food_bk  WHERE order_id BETWEEN ? AND ? AND food_id =?";
            return Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), minId, maxId, food_id).toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 获取当天的最热食品
     * @return
     */
    @Override
    public OrderFood findPopularFood(String minId, String maxId) {
        try {
            sql = "SELECT * FROM order_food_bk  WHERE order_id BETWEEN ? AND ?  GROUP BY food_id  HAVING order_food_bk.food_id NOT IN"+NOSORTFOODIDS+ "ORDER BY SUM(food_count_bk) DESC LIMIT 1";
            return queryRunner.query(sql, new BeanHandler<>(OrderFood.class, rowProcessor), minId, maxId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new OrderFood();
    }
}
